﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
using KBCommon.DBUtility;

namespace KBCommon.Utility
{
    public class Excel
    {
        //public static ToExcel()
        //{
        //    SaveFileDialog dialog = new SaveFileDialog();
        //    dialog.Filter = "Excel表格（*.xlsx）|*.xlsx";
        //    dialog.RestoreDirectory = true;
        //    if (dialog.ShowDialog() == DialogResult.OK)
        //    {
        //        string localFilePath = dialog.FileName.ToString(); //获得文件路径 
        //        //string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1); //获取文件名，不带路
        //        //获取文件路径，不带文件名 
        //        //FilePath = localFilePath.Substring(0, localFilePath.LastIndexOf("\\"));
        //        MyGrid.SaveGrid(localFilePath, FileFormatEnum.Excel);
        //        MsgHelper.ShowInformationMsgBox("导出文件成功！");
        //    }
        //}


        #region 把Excel文件的数据导入数据库
        /// <summary>
        /// 把Excel文件的数据导入数据库
        /// </summary>
        /// <param name="sqlTableName">数据库里的表名</param>
        /// <param name="strFileName">Excel路径</param>
        /// <returns>返回受影响行数,如果返回null说明操作出错退出</returns>
        public int? GetExeclToSQL(string sqlTableName, string ExcelPath)
        {
            IDBHelper db = KBCommon.DBUtility.DBFactory.CreateDb();
            string sql = "exec sp_columns '" + sqlTableName + "'";
            DataSet ds = db.Query(sql);//查询数据表的结结构
            string field = "";
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)//循环把COLUMN_NAME列（数据表的列名）的值加入field
            {
                if (string.IsNullOrEmpty(field))
                {
                    field = ds.Tables[0].Rows[i]["COLUMN_NAME"].ToString();
                }
                else
                {
                    field += "," + ds.Tables[0].Rows[i]["COLUMN_NAME"].ToString();
                }
            }
            string[] ColunmNames;//数据库里面表的字段数组
            if (!string.IsNullOrEmpty(field))
            {
                field += ",,";
                ColunmNames = field.Split(',');
            }
            else
            {
                return null;
            }

            string iniPath = @"xlstobill.ini";
            string iniFullPath = Path.GetFullPath(iniPath);
            if (!File.Exists(iniFullPath))
            {
                MsgHelper.ShowInformationMsgBox("xlstobill.ini 文件不存在，请找开发人员要。");
                return null;
            }
            KBCommon.Utility.INIFile inif = new INIFile(iniFullPath);//读取ini文件

            string fieldname = "";//英文列名
            string fieldCName = "";//中文列名
            foreach (string ColunmName in ColunmNames)
            {
                if (!string.IsNullOrEmpty(ColunmName))
                {
                    string iniCName = inif.IniReadValue(sqlTableName, ColunmName);//给Section(表名)和key(列名)得到中文列名
                    if (!string.IsNullOrEmpty(iniCName))
                    {
                        if (string.IsNullOrEmpty(fieldname))
                        {
                            fieldname = ColunmName;
                            fieldCName = iniCName;
                        }
                        else
                        {
                            fieldname += "," + ColunmName;
                            fieldCName += "," + iniCName;
                        }
                    }
                }
            }

            DataSet Excelds = OpenExcel(ExcelPath, fieldCName);//读取Excel文件放到DataSet里
            if (Excelds == null)
            {
                MsgHelper.ShowInformationMsgBox("你的Excel文件不存在数据或与要导入的数据表结构不对！请重新确认");
                return null;
            }
            int isertColumnNum = 0;
            for (int i = 0; i < Excelds.Tables[0].Rows.Count; i++)
            {
                object[] obj = Excelds.Tables[0].Rows[i].ItemArray;//要插入的一行数据的值数组
                string strObj = "";//接收插入数据的字符串待拼成sql语句
                foreach (object columnObj in obj)//遍历数组拼成字符串
                {
                    if (string.IsNullOrEmpty(strObj))
                    {
                        if (columnObj.GetType().Name == "String" || columnObj.GetType().Name == "string")//值为字符串的加上单引号
                        {
                            strObj += "'" + columnObj + "'";
                        }
                        else
                        {
                            strObj += columnObj;
                        }
                    }
                    else
                    {
                        if (columnObj.GetType().Name == "String" || columnObj.GetType().Name == "string")
                        {
                            strObj += "," + "'" + columnObj + "'";
                        }
                        else
                        {
                            strObj += "," + columnObj;
                        }
                    }
                }
                string strIsertSQL = "insert into " + sqlTableName + "(" + fieldname + ") values(" + strObj + ")";//拼成Isert语句
                string strSelectSQL = "select * from " + sqlTableName + " where " + fieldname.Split(',')[0] + " = " + strObj.Split(',')[0];
                string strDeleteSQL = "delete from " + sqlTableName + " where " + fieldname.Split(',')[0] + " = " + strObj.Split(',')[0];
               // int n = db.Query(strSelectSQL).Tables[0].Rows.Count;
                if (db.Query(strSelectSQL).Tables[0].Rows.Count > 0)
                {
                    db.ExecuteSql(strDeleteSQL);
                    isertColumnNum += db.ExecuteSql(strIsertSQL);
                }
                else
                {
                    isertColumnNum += db.ExecuteSql(strIsertSQL);
                }
            }
            return isertColumnNum;//返回受影响行数

        }

        /// <summary>
        /// 读取Excel
        /// </summary>
        /// <param name="ExcelPath">Excel文件的路径</param>
        /// <param name="fieldCName">Excel文件里表的中文列名</param>
        /// <returns>Excel文件里的表数据</returns>
        DataSet OpenExcel(string ExcelPath, string fieldCName)
        {
            DataSet ds = new DataSet();
            string strConn="";
            string[] edition = ExcelPath.Trim().Split('.');
            if (edition[edition.Length-1].ToLower() == "xls")
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelPath + ";Extended Properties=Excel 8.0;";
            }
            else if (edition[edition.Length - 1].ToLower() == "xlsx")
            {
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelPath + ";Extended Properties=\"EXCEL 12.0;HDR=YES\"";
            }
            
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                using (OleDbDataAdapter comm = new OleDbDataAdapter("SELECT " + fieldCName + " FROM [Sheet1$]", strConn))
                {
                    try
                    {
                        comm.Fill(ds);
                    }
                    catch (Exception ex)
                    {
                        MsgHelper.ShowInformationMsgBox(ex.Message);
                        return null;
                    }

                }
            }
            return ds;
        }
        #endregion



    }
}
